MSSQL Aggregaties

Bronnen

Aggregate Functions (Transact-SQL), MSDN

Definitie

Aggregate functions perform a calculation on a set of values and return a single value. Aggregate functions are frequently used with the GROUP BY clause of the SELECT statement.

All aggregate functions are deterministic. This means aggregate functions return the same value any time that they are called by using a specific set of input values.

Stappenplan

Om met deze functies te leren werken wijzigen we onze Boeken tabel.

Opdracht

1. Voeg een kolom voor de leeftijd toe aan Boeken. Met de leeftijd moet je kunnen rekenen. Sla de script op in het bestand met de naam BoekenAlterLeeftijdAddAndUpdate.sql.
2. Insert de leeftijd en gebruik hiervoor een random number generator. Sla deze scipt op in BoekenLeeftijdAddAndUpdate.sql.

update Boeken set Leeftijd = round(100*rand(), 0)
-- even kijken hoe een random number genereert
select rand()

Dat werkt niet omdat we maar één keer een random getal genereren en dan daarmee alle leeftijd kolommen invullen.

We gebruiken nu een andere manier om een randomgetal te genereren:

update Boeken set Leeftijd = 2014-Vershnijvingsdatum
go

Ellen komt met een betere oplossing:

-- je genereert een random getal,
-- dit geeft voor elke rij een andere waarde
update Boeken set Leeftijd = ROUND(100 *RAND(convert(varbinary, newid())), 0)
go

Je genereert een random getal, maar krijgt op elke rij dezelfde waarde

update Boeken set Leeftijd = round(100*rand(), 0);

Jim merkt op dat er auteurs tussen zitten van 1 en 2 jaar oud. We passen daar een mouw aan met het volgende iif statement:

update Boeken
set Leeftijd = iif(Leeftijd <=12, Leeftijd + 20, Leeftijd)
go

Met de immediate if kan je een if blok op één lijn schrijven en dus gemakkelijk insluiten in een SQL statement.

1. Het gemiddelde berekenen
   Bereken het gemiddelde (average) leeftijd van de auteurs met de AVG functie. Alle oefeningen met aggregate functies slaan we op in een bestand met BoekenAggregateFuncties.sql.

   use OualidYousfi
   go
   select AVG(leeftijd)vas [Gemiddelde leeftijd] from Boeken

   Let op het gebruik van as. Met de instructie as kan je een andere naam aan de geselecteerde kolom geven.

2. De grootste waarde in een kolom vinden

   Vind de oudste auteur in de tabel boeken. Gebruik hiervoor de aggregate functie MAX.

   -- toon de oudste leeftijd
   select MAX(leeftijd)as [Oudste auteur] from Boeken

   Ik weet nu wel de oudste leeftijd, maar ik weet niet wie. Hoe los je dat op?

   Als we gewoon de voornaam en familienaam als kolommen toevoegen krijgen we foutmelding:

   select max(leeftijd) as [Leeftijd], Voornaam, Familienaam from Boeken
   
   Foutmelding:
   
   Msg 8120, Level 16, State 1, Line 8
   Column 'Boeken.Voornaam' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

   De aggregaat functie retourneert slechts één enkele waarde en kan dus geen rij retourneren.

   Ik wil toch ook de naam van de oudste auteur.

   Ik kan dat met twee queries na mekaar uit te voeren. Eerst zoek ik de oudste leeftijd en die geef ik letterlijk door aan de where clausule van de tweede querie:

   select max(leeftijd) as [Oudste auteur] from Boeken
   go
   select Voornaam, Familienaam, Leeftijd from Boeken where Leeftijd=100
   go

   Maar in plaats van de waarde manueel door te geven, kan ik de eerste query als een subquery in de where clausule van de tweede query stoppen:

   -- toon de oudste auteur
   -- distinct, indien er meerdere
   -- boeken van een zelfde
   -- auteur in de tabel zitten,
   -- willen we toch maar
   -- één keer de naam van de auteur tonen
   select Voornaam, Familienaam from Boeken 
      where Leeftijd =(select max(Leeftijd) from Boeken)
   go

   De code werkt maar de select max wordt voor elke rij herhaald.

   De volgende code wel voert de select max slechts één keer uit:

   use MarcTuinstra
   go
   
   declare @max int
   set @max = (select max(Leeftijd) from Boeken)
   
   select distinct Voornaam, Familienaam from Boeken
      where Leeftijd = @max
   go

   Een variabele naam in SQL begint altijd met een @. Declareren doe je met de instructie declare en toekenen met de instructie set.

3. De minimumwaarde in een kolom
   Toon de jongste leeftijd in de tabel Boeken.
   Toon de voornaam en de naam van de jongste auteur.
4. De optelsom van alle waarden in een kolom

   select sum(Leeftijd) from Boeken